package demo;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.management.Query;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;


public class JDBCTest {
    public static void main(String[] args) throws Exception{
        findAll();
        insertInfo();
        deleteInfo();
        updateInfo();
        findAll();
    }

    private static void updateInfo() throws SQLException {
        //获取数据源，获取连接池
        DataSource dataSource=JDBCUtil.getDataSource();
        //创建QueryRunner对象,封装了数据库连接池
        QueryRunner runner=new QueryRunner(dataSource);
        System.out.println("要改数据的eid:");
        String eid=new Scanner(System.in).nextLine();
        System.out.println("更改后的name:");
        String name=new Scanner(System.in).nextLine();
        System.out.println("更改后的age:");
        int age=new Scanner(System.in).nextInt();
        System.out.println("更改后的department:");
        String department=new Scanner(System.in).nextLine();
        System.out.println("更改好的salary:");
        double salary=new Scanner(System.in).nextDouble();
        String sql="update employee set name=?,age=?,department=?,salary=? where eid=?";
        runner.update(sql,name,age,department,salary,eid);

    }

    private static void deleteInfo() throws SQLException {
        System.out.println("eid:");
        String eid=new Scanner(System.in).nextLine();
        DataSource dataSource=JDBCUtil.getDataSource();
        //创建QueryRunner对象,封装了数据库连接池
        QueryRunner runner=new QueryRunner(dataSource);
        String sql="delete from employee where eid=?";
        int count=runner.update(sql,eid);
        if(count>0)
        {
            System.out.println("删除成功");
        }
        else{
            System.out.println("删除失败");
        }
    }

    private static void insertInfo() throws SQLException {
          Scanner sc=new Scanner(System.in);
        System.out.println("eid:");
        String eid=sc.next();
        System.out.println("name:");
        String name=sc.nextLine();
        System.out.println("age:");
        int age=sc.nextInt();
        System.out.println("department:");
        String department=sc.nextLine();
        System.out.println("salary:");
        double salary=sc.nextDouble();
        DataSource dataSource=JDBCUtil.getDataSource();
        QueryRunner runner=new QueryRunner(dataSource);
        String sql="insert into employee values(null,?,?,?,?,?)";
        runner.update(sql,eid,name,age,department,salary);
    }

    private static void findAll() throws SQLException {
        DataSource dataSource=JDBCUtil.getDataSource();
        QueryRunner runner=new QueryRunner(dataSource);
        String sql="select * from employee";
        //查询
       /* runner.query(sql, new ResultSetHandler<Object>() {
            @Override
            public Object handle(ResultSet rs) throws SQLException {
                while (rs.next()){
                  String eid=rs.getString("eid");
                  String name=rs.getString("name");
                  System.out.println(eid+"=="+name);
                }
                return null;
            }
        });*/
       List<Employee> emps=runner.query(sql,new BeanListHandler<Employee>(Employee.class));
       for (Employee emp:emps){
           System.out.println(emp);
       }
    }
}
